package com.wmx.sqliteapp3.sqlite;

import cn.hutool.core.date.DateUtil;
import com.wmx.sqliteapp3.model.ResultCode;
import com.wmx.sqliteapp3.model.ResultData;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * SQLite - 轻量级数据库管理系统，遵守ACID，非常适合于需要一个简单且独立的数据库解决方案的应用。
 * 原生JDBC使用示例
 *
 * @author wangMaoXiong
 * @version 1.0
 * @date 2025/1/18 15:52
 */
@RestController
public class SqliteController {

    private static final Logger log = LoggerFactory.getLogger(SqliteController.class);

    @Value("${app.sqlite.db-0-path:data/db0.db}")
    private String db0Path;

    /**
     * 原生JDBC-获取连接
     * http://localhost:8080/sqlite/getConnection
     */
    @GetMapping("sqlite/getConnection")
    public ResultData<Object> getConnection() {
        // 示例1：jdbc:sqlite:data/db0.db 前缀固定，后缀为数据库文件路径，可以是相对路径或者绝对路径，存放.db文件的目录必须存在，否则报错，.db文件不存在时，自动新建;
        String url = "jdbc:sqlite:" + db0Path;
        Map<String, Object> dataMap = new LinkedHashMap<>();
        // 获取连接成功，就会自动创建数据库.db文件;
        try (Connection connection = DriverManager.getConnection(url)) {
            // 连接成功，可以执行数据库操作
            dataMap.put("databaseProductName", connection.getMetaData().getDatabaseProductName());
            dataMap.put("databaseProductVersion", connection.getMetaData().getDatabaseProductVersion());
            dataMap.put("url", connection.getMetaData().getURL());
            dataMap.put("userName", connection.getMetaData().getUserName());
            dataMap.put("autoCommit", connection.getAutoCommit());
            return ResultData.success(dataMap);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
    }

    /**
     * 原生JDBC-创建表
     * http://localhost:8080/sqlite/createTable
     */
    @GetMapping("sqlite/createTable")
    public ResultData<Object> createTable() {
        String url = "jdbc:sqlite:" + db0Path;
        try (Connection connection = DriverManager.getConnection(url);
             Statement statement = connection.createStatement()) {
            String sql = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "name TEXT NOT NULL, " +
                    "age INTEGER NOT NULL, " +
                    "annual_salary float(18,2) NOT NULL, " +
                    "create_time INTEGER NOT NULL, " + // 时间类型直接使用数字或者字符串存储
                    "update_time INTEGER NOT NULL" +
                    ")";
            statement.execute(sql);
            return ResultData.success("建表成功：" + sql);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
    }

    /**
     * 原生JDBC-删除表
     * http://localhost:8080/sqlite/dropTable?tableName=users
     */
    @GetMapping("sqlite/dropTable")
    public ResultData<Object> dropTable(String tableName) {
        String url = "jdbc:sqlite:" + db0Path;
        try (Connection connection = DriverManager.getConnection(url);
             Statement statement = connection.createStatement()) {
            String sql = "DROP TABLE IF EXISTS " + tableName;
            statement.execute(sql);
            return ResultData.success("删表成功：" + sql);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
    }

    /**
     * 原生JDBC-新增数据
     * http://localhost:8080/sqlite/addUser?name=Poni&age=44&annualSalary=8888.64
     * http://localhost:8080/sqlite/addUser?name=张三&age=35&annualSalary=6888.34
     * http://localhost:8080/sqlite/addUser?name=李四&age=30&annualSalary=7888.74
     */
    @GetMapping("sqlite/addUser")
    public ResultData<Object> insertUser(String name, int age, float annualSalary) {
        String url = "jdbc:sqlite:" + db0Path;
        String sql = "INSERT INTO users(name, age, annual_salary, create_time, update_time) VALUES(?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setString(1, name);
            statement.setInt(2, age);
            statement.setFloat(3, annualSalary);
            statement.setLong(4, DateUtil.date().getTime());
            statement.setLong(5, DateUtil.date().getTime());
            statement.executeUpdate();
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
        return ResultData.success("新增成功");
    }

    /**
     * 原生JDBC-查询数据
     * http://localhost:8080/sqlite/findAll
     */
    @GetMapping("sqlite/findAll")
    public ResultData<Object> findAll() {
        String url = "jdbc:sqlite:" + db0Path;
        List<Map<String, Object>> dataList = new ArrayList<>();
        String sql = "SELECT * FROM users";
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement statement = conn.prepareStatement(sql);
             ResultSet rs = statement.executeQuery()) {
            dataList = resultSetToList(rs);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
        return ResultData.success(dataList);
    }

    /**
     * 将 resultSetToList 结果集转为 List<Map<String, Object>>
     *
     * @param resultSet ：没有查询到值时，不会为 null，只是游标 next 不会走，
     * @return ：返回的 map key(字段名称) 统一转为小写。
     * @throws SQLException
     */
    private static List<Map<String, Object>> resultSetToList(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        // 结果集元数据对象，可以用于获取表的列信息，即使没有查询到值，列信息同样会有
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnCount = resultSetMetaData.getColumnCount();
        List<String> dateFields = new ArrayList<>();
        dateFields.add("create_time");
        dateFields.add("update_time");
        while (resultSet.next()) {
            Map<String, Object> rowData = new LinkedHashMap<>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                Object object = resultSet.getObject(i);
                String fieldName = resultSetMetaData.getColumnName(i).toLowerCase();
                if (object != null) {
                    if (object instanceof java.util.Date) {
                        // 将时间格式化为"yyyy-MM-dd HH:mm:ss"
                        object = DateFormatUtils.format((java.util.Date) object, "yyyy-MM-dd HH:mm:ss");
                    } else if (dateFields.contains(fieldName) && object instanceof Long) {
                        // 将时间格式化为"yyyy-MM-dd HH:mm:ss"
                        java.util.Date date = new java.util.Date((Long) object);
                        object = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
                    }
                }
                rowData.put(fieldName, object);
            }
            list.add(rowData);
        }
        return list;
    }

    /**
     * 原生JDBC-更新数据
     * http://localhost:8080/sqlite/updateUser?name=王五&age=23&id=4
     */
    @GetMapping("sqlite/updateUser")
    public ResultData<Object> updateUser(String name, int age, long id) {
        String url = "jdbc:sqlite:" + db0Path;
        String sql = "UPDATE users SET age =?,name =?,update_time=? WHERE id =? ";
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setInt(1, age);
            statement.setString(2, name);
            statement.setLong(3, DateUtil.date().getTime());
            statement.setInt(4, Integer.parseInt(String.valueOf(id)));
            int i = statement.executeUpdate();
            return ResultData.success("更新成功:" + i);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
    }

    /**
     * 原生JDBC-删除数据
     * http://localhost:8080/sqlite/deleteUser?id=4
     */
    @GetMapping("sqlite/deleteUser")
    public ResultData<Object> deleteUser(long id) {
        String url = "jdbc:sqlite:" + db0Path;
        String sql = "DELETE FROM users WHERE id = ?";
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement statement = conn.prepareStatement(sql)) {
            statement.setInt(1, Integer.parseInt(String.valueOf(id)));
            int i = statement.executeUpdate();
            return ResultData.success("删除成功:" + i);
        } catch (Exception e) {
            log.error(ExceptionUtils.getStackTrace(e));
            return ResultData.error(ResultCode.FAIL, e);
        }
    }

}
